package com.zxw.html.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.springframework.util.StringUtils;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

/**
 * @author Xuewei.Zhang
 * @date 2023/1/6
 */
public class HtmlToExcel {


    public static void main(String[] args) throws IOException {
        String html = "<table class=\"el-table__header\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"width: 4636px;\"><colgroup><col name=\"el-table_1_column_1\" width=\"55\"><col name=\"el-table_1_column_2\" width=\"130\"><col name=\"el-table_1_column_3\" width=\"80\"><col name=\"el-table_1_column_4\" width=\"166\"><col name=\"el-table_1_column_5\" width=\"166\"><col name=\"el-table_1_column_6\" width=\"103\"><col name=\"el-table_1_column_7\" width=\"120\"><col name=\"el-table_1_column_8\" width=\"80\"><col name=\"el-table_1_column_9\" width=\"103\"><col name=\"el-table_1_column_10\" width=\"156\"><col name=\"el-table_1_column_11\" width=\"100\"><col name=\"el-table_1_column_12\" width=\"159\"><col name=\"el-table_1_column_13\" width=\"278\"><col name=\"el-table_1_column_14\" width=\"80\"><col name=\"el-table_1_column_15\" width=\"80\"><col name=\"el-table_1_column_16\" width=\"80\"><col name=\"el-table_1_column_17\" width=\"80\"><col name=\"el-table_1_column_18\" width=\"80\"><col name=\"el-table_1_column_19\" width=\"182\"><col name=\"el-table_1_column_20\" width=\"80\"><col name=\"el-table_1_column_21\" width=\"250\"><col name=\"el-table_1_column_22\" width=\"130\"><col name=\"el-table_1_column_23\" width=\"90\"><col name=\"el-table_1_column_24\" width=\"130\"><col name=\"el-table_1_column_25\" width=\"100\"><col name=\"el-table_1_column_26\" width=\"250\"><col name=\"el-table_1_column_27\" width=\"250\"><col name=\"el-table_1_column_28\" width=\"103\"><col name=\"el-table_1_column_29\" width=\"167\"><col name=\"el-table_1_column_30\" width=\"103\"><col name=\"el-table_1_column_31\" width=\"140\"><col name=\"el-table_1_column_32\" width=\"156\"><col name=\"el-table_1_column_33\" width=\"156\"><col name=\"el-table_1_column_34\" width=\"80\"><col name=\"el-table_1_column_35\" width=\"173\"></colgroup><thead class=\"\"><tr class=\"\"><th class=\"el-table_1_column_1 is-center el-table-column--selection is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\"><label class=\"el-checkbox el-checkbox--default\"><span class=\"el-checkbox__input\"><input class=\"el-checkbox__original\" type=\"checkbox\" aria-hidden=\"false\" value=\"false\" id=\"el-id-3824-54\"><span class=\"el-checkbox__inner\"></span></span><!--v-if--></label><!----><!----></div></th><th class=\"el-table_1_column_2 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">单项细分<!----><!----></div></th><th class=\"el-table_1_column_3 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">收款分类<!----><!----></div></th><th class=\"el-table_1_column_4 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">户名<!----><!----></div></th><th class=\"el-table_1_column_5 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">业务基础订单号<!----><!----></div></th><th class=\"el-table_1_column_6 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">业务描述<!----><!----></div></th><th class=\"el-table_1_column_7 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">交易金额<!----><!----></div></th><th class=\"el-table_1_column_8 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">核销金额<!----><!----></div></th><th class=\"el-table_1_column_9 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">未核销金额<!----><!----></div></th><th class=\"el-table_1_column_10 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">审核时间<!----><!----></div></th><th class=\"el-table_1_column_11 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">审核名称<!----><!----></div></th><th class=\"el-table_1_column_12 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">账号<!----><!----></div></th><th class=\"el-table_1_column_13 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">开户行名<!----><!----></div></th><th class=\"el-table_1_column_14 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">单据状态<!----><!----></div></th><th class=\"el-table_1_column_15 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">资金划拨<!----><!----></div></th><th class=\"el-table_1_column_16 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">现转标识<!----><!----></div></th><th class=\"el-table_1_column_17 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">收支方向<!----><!----></div></th><th class=\"el-table_1_column_18 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">业务类型<!----><!----></div></th><th class=\"el-table_1_column_19 is-left is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">对方账户<!----><!----></div></th><th class=\"el-table_1_column_20 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">客户编码<!----><!----></div></th><th class=\"el-table_1_column_21 is-left is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">客户名称<!----><!----></div></th><th class=\"el-table_1_column_22 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">银行交易明细类型<!----><!----></div></th><th class=\"el-table_1_column_23 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">集团内交易<!----><!----></div></th><th class=\"el-table_1_column_24 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">对公、对私标志<!----><!----></div></th><th class=\"el-table_1_column_25 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">nc币种名称<!----><!----></div></th><th class=\"el-table_1_column_26 is-left is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">对方开户行名<!----><!----></div></th><th class=\"el-table_1_column_27 is-left is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">对方户名<!----><!----></div></th><th class=\"el-table_1_column_28 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">对方银行类别<!----><!----></div></th><th class=\"el-table_1_column_29 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">销售组织<!----><!----></div></th><th class=\"el-table_1_column_30 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">支付匹配号<!----><!----></div></th><th class=\"el-table_1_column_31 is-left is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">交易备注<!----><!----></div></th><th class=\"el-table_1_column_32 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">交易时间<!----><!----></div></th><th class=\"el-table_1_column_33 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">数据更新时间<!----><!----></div></th><th class=\"el-table_1_column_34 is-center is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center;\"><div class=\"cell\">用途<!----><!----></div></th><th class=\"el-table_1_column_35 is-center small-padding fixed-width el-table-fixed-column--right is-first-column is-leaf el-table__cell\" colspan=\"1\" rowspan=\"1\" style=\"text-align: center; right: 0px;\"><div class=\"cell\">操作<!----><!----></div></th></tr></thead></table>11";
        List<String> htmls = new ArrayList<String>() {{
            add(html);
        }};

        HtmlToExcel.toExcel(htmls, "测试", "E:\\excel\\");
    }

    /**
     * @param htmls     字符串的html
     * @param sheetName sheet页的名字
     * @param filePath  保存地址
     * @throws IOException
     */
    public static String toExcel(List<String> htmls, String sheetName, String filePath) throws IOException {
        String fileName = System.currentTimeMillis() + ".xlsx";
        HSSFWorkbook wb = new HSSFWorkbook();

        //表头单元格风格
        HSSFCellStyle thStyle = wb.createCellStyle();
        thStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        thStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //上下左右居中
        thStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        thStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //边框
        thStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        thStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        thStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        thStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        thStyle.setTopBorderColor(IndexedColors.WHITE.getIndex());
        thStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
        thStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex());
        thStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
        //字体
        Font thFont = wb.createFont();
        thFont.setColor(IndexedColors.BLACK.getIndex());
        thFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        thStyle.setFont(thFont);
        Sheet sheet = wb.createSheet(sheetName);

        int index = 0;
        for (int i = 0; i < htmls.size(); i++) {
            String html = htmls.get(i);
            if (StringUtils.isEmpty(html)) {
                continue;
            }
            //获取html的数据
            List<List<Map<String, String>>> excelData = getExcelData(html);
            //处理数据
            for (int rowNum = 0; rowNum < excelData.size(); rowNum++) {
                index++;
                //外层是循环行，每循环一次，创建一个行的对象
                Row row = sheet.createRow(index);
                //设置行的高度
                row.setHeightInPoints(25);
                for (int cellNum = 0; cellNum < excelData.get(rowNum).size(); cellNum++) {

                    //处理跨行跨列
                    if ((excelData.get(rowNum).get(cellNum).get("colspanValue") != null) && (excelData.get(rowNum).get(cellNum).get("rowspanValue") != null)) {
                        int colspanValue = Integer.parseInt(excelData.get(rowNum).get(cellNum).get("colspanValue"));
                        int rowspanValue = Integer.parseInt(excelData.get(rowNum).get(cellNum).get("rowspanValue"));
                        sheet.addMergedRegion(new CellRangeAddress(index, rowspanValue + index - 1, cellNum, cellNum + colspanValue - 1));
                    } else {
                        if (excelData.get(rowNum).get(cellNum).get("colspanValue") != null) {
                            int colspanValue = Integer.parseInt(excelData.get(rowNum).get(cellNum).get("colspanValue"));
                            sheet.addMergedRegion(new CellRangeAddress(index, index, cellNum, cellNum + colspanValue - 1));
                        }
                        if (excelData.get(rowNum).get(cellNum).get("rowspanValue") != null) {
                            int rowspanValue = Integer.parseInt(excelData.get(rowNum).get(cellNum).get("rowspanValue"));
                            sheet.addMergedRegion(new CellRangeAddress(index, rowspanValue + index - 1, cellNum, cellNum));
                        }
                    }

                    //内层循环每行的单元格，每循环一次，创建一个单元格的对象
                    Cell cell = row.createCell(cellNum);

                    //赋值
                    cell.setCellValue(excelData.get(rowNum).get(cellNum).get("value"));

                    //设置样式
                    if (excelData.get(rowNum).get(cellNum).get("style") != null) {
                        if ("th".equals(excelData.get(rowNum).get(cellNum).get("style"))) {
                            cell.setCellStyle(thStyle);
                        }
                        if ("td".equals(excelData.get(rowNum).get(cellNum).get("style"))) {

                            //表体
                            HSSFCellStyle tdStyle = wb.createCellStyle();
                            tdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                            //上下左右居中
                            tdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                            tdStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                            //边框
                            tdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
                            tdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
                            tdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                            tdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                            tdStyle.setTopBorderColor(IndexedColors.WHITE.getIndex());
                            tdStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
                            tdStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex());
                            tdStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());

                            if (rowNum % 2 == 0) {
                                tdStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
                            } else {
                                tdStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                            }
                            cell.setCellStyle(tdStyle);
                        }
                    }

                    //设置宽度
                    sheet.setColumnWidth(cellNum, (excelData.get(rowNum).get(cellNum).get("value").length() + 20) * 256);
                }
            }
        }


        HSSFPalette palette = wb.getCustomPalette();

        //获取配置的颜色
        palette.setColorAtIndex(IndexedColors.ORANGE.getIndex(),
                (byte) 97, //RGB red (0-255)
                (byte) 191, //RGB green
                (byte) 130 //RGB blue
        );
        palette.setColorAtIndex(IndexedColors.BLUE.getIndex(),
                (byte) 233, //RGB red (0-255)
                (byte) 244, //RGB green
                (byte) 232 //RGB blue
        );
        String lastPath = filePath + fileName;
        try (OutputStream fileOut = new FileOutputStream(lastPath)) {
            wb.write(fileOut);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return fileName;
    }

    public static List<List<Map<String, String>>> getExcelData(String tableHtml) {

        Document document = Jsoup.parse(tableHtml);

        //取得表体的html
        Elements tbody = document.select("tbody").select("tr");

        //获取table的最大的列数
        List<Integer> tdSize = new ArrayList<>();
        for (Element element : tbody) {
            tdSize.add(element.select("td").size());
        }
        Collections.sort(tdSize);

        //最后一行就是整个表格最大的列数
        int rowCellNum = tdSize.get(tdSize.size() - 1);

        //整个table的<tr>
        Elements trAll = document.select("tr");
        //tr就是整个表格的行数
        int excelDataSize = trAll.size();

        //存放表格数据
        List<List<Map<String, String>>> excelData = new ArrayList<>();

        //先创建空的excel数据
        for (int i = 0; i < excelDataSize; i++) {
            excelData.add(new ArrayList<>());
            for (int j = 0; j < rowCellNum; j++) {
                excelData.get(i).add(new HashMap<>());
            }
        }

        //按行数进行循环
        for (int rowNum = 0; rowNum < excelData.size(); rowNum++) {
            //取得每一行的html
            Element element = trAll.get(rowNum);

            //这一行的表头
            Elements th = element.select("th");
            Elements td = element.select("td");

            //列数
            int index = 0;

            //循环一行的数据
            for (int cellNum = 0; cellNum < excelData.get(rowNum).size(); cellNum++) {

                //只有value==null的才是没有被赋值的,因为跨行跨列的已经赋值过了
                if (excelData.get(rowNum).get(cellNum).get("value") == null) {
                    //取一个单元格对象
                    Element cell = null;
                    //样式的标识
                    String style = "";

                    //表头th处理
                    if (th.size() != 0) {
                        //这是表头的样式
                        style = "th";
                        excelData.get(rowNum).get(cellNum).put("style", style);
                        if (th.size() > index) cell = th.get(index);
                    }

                    //表体td处理
                    if (td.size() != 0) {
                        //这是表体的样式
                        style = "td";
                        excelData.get(rowNum).get(cellNum).put("style", style);
                        if (td.size() > index) cell = td.get(index);
                    }
                    //单元格的值
                    String value = cell == null ? "" : cell.text();

                    //处理跨行跨列
                    String cellHtml = cell == null ? "" : cell.outerHtml();
                    //rowspan和colspan都有值的情况下
                    if ((cellHtml.indexOf("rowspan") != -1 && cellHtml.indexOf("\"\"") == -1) || (cellHtml.indexOf("colspan") != -1 && cellHtml.indexOf("\"\"") == -1)) {
                        String rowspanValue = "0";
                        String colspanValue = "0";

                        //rowspan有值 取值并给到单元格对象
                        if (cellHtml.indexOf("rowspan") != -1 && cellHtml.indexOf("\"\"") == -1) {
                            String rowspanValueStr = cellHtml.substring(cellHtml.indexOf("rowspan") + 7);
                            rowspanValueStr = rowspanValueStr.substring(rowspanValueStr.indexOf("\"") + 1);
                            rowspanValueStr = rowspanValueStr.substring(0, rowspanValueStr.indexOf("\""));
                            rowspanValue = rowspanValueStr;
                            excelData.get(rowNum).get(cellNum).put("rowspanValue", rowspanValue);
                        }
                        //colspan有值 取值并给到单元格对象
                        if (cellHtml.indexOf("colspan") != -1 && cellHtml.indexOf("\"\"") == -1) {
                            String colspanValueStr = cellHtml.substring(cellHtml.indexOf("colspan") + 7);
                            colspanValueStr = colspanValueStr.substring(colspanValueStr.indexOf("\"") + 1);
                            colspanValueStr = colspanValueStr.substring(0, colspanValueStr.indexOf("\""));
                            colspanValue = colspanValueStr;
                            excelData.get(rowNum).get(cellNum).put("colspanValue", colspanValue);
                        }

                        //这个单元格又跨行又跨列
                        if (Integer.parseInt(rowspanValue) > 0 && Integer.parseInt(colspanValue) > 0) {

                            //把他跨列的单元格给赋上值 , 没有赋值就是null  ,  和559行的逻辑呼应
                            for (int i = 1; i < Integer.parseInt(rowspanValue); i++) {
                                excelData.get(rowNum + i).get(cellNum).put("value", value);
                                excelData.get(rowNum + i).get(cellNum).put("style", style);

                                //把他跨行的单元格给赋上值 , 没有赋值就是null  ,  和559行的逻辑呼应
                                for (int j = 1; j < Integer.parseInt(colspanValue); j++) {
                                    excelData.get(rowNum).get(cellNum + j).put("value", value);
                                    excelData.get(rowNum).get(cellNum + j).put("style", style);
                                    excelData.get(rowNum + i).get(cellNum + j).put("value", value);
                                    excelData.get(rowNum + i).get(cellNum + j).put("style", style);
                                }
                            }
                        }
                        //跨行或者跨列
                        else {
                            //跨列
                            if (Integer.parseInt(rowspanValue) > 0) {
                                //把他跨列的单元格给赋上值 , 没有赋值就是null  ,  和559行的逻辑呼应
                                for (int i = 1; i < Integer.parseInt(rowspanValue); i++) {
                                    excelData.get(rowNum + i).get(cellNum).put("value", value);
                                    excelData.get(rowNum + i).get(cellNum).put("style", style);
                                }
                            }
                            if (Integer.parseInt(colspanValue) > 0) {
                                //把他跨行的单元格给赋上值 , 没有赋值就是null  ,  和559行的逻辑呼应
                                for (int j = 1; j < Integer.parseInt(colspanValue); j++) {
                                    if (excelData.size() > 0) {
                                        if (j == excelData.get(0).size()) {
                                            break;
                                        }
                                    }
                                    excelData.get(rowNum).get(cellNum + j).put("value", value);
                                    excelData.get(rowNum).get(cellNum + j).put("style", style);
                                }
                            }
                        }
                    }
                    excelData.get(rowNum).get(cellNum).put("value", value);
                    index = index + 1;
                }
            }
        }
        return excelData;
    }


}
